#include "jsonhpp/json.hpp"
#include "util/macro_expand.hpp"
#include "util/string_format.hpp"
#include "util/string_scan.hpp"

#include <array>
#include <cstdint>
#include <deque>
#include <forward_list>
#include <initializer_list>
#include <iomanip>
#include <istream>
#include <list>
#include <map>
#include <memory>
#include <ostream>
#include <queue>
#include <set>
#include <unordered_map>
#include <unordered_set>
#include <vector>

namespace kratos {
namespace util {
namespace db {
namespace _mysql {

struct Date {
  int year{0};
  int month{0};
  int day{0};
  inline constexpr static const char *const fmt = "{}-{}-{}";
  Date(const std::string &s = "0000-00-00") { sscan(fmt, s, year, month, day); }
  std::string get_db_type() const { return "DATE"; }
  std::string get_db_default() const { return "'0000-00-00'"; }
  friend std::ostream &operator<<(std::ostream &os, const Date &obj) {
    return os << "'" << std::setw(4) << std::setfill('0') << obj.year << "-"
              << std::setw(2) << std::setfill('0') << obj.month << "-"
              << std::setw(2) << std::setfill('0') << obj.day << "'";
  }
};

struct Time {
  int hour{0};
  int minute{0};
  int second{0};
  inline constexpr static const char *const fmt = "{}:{}:{}";
  Time(const std::string &s = "00:00:00") {
    sscan(fmt, s, hour, minute, second);
  }
  std::string get_db_type() const { return "TIME"; }
  std::string get_db_default() const { return "'00:00:00'"; }
  friend std::ostream &operator<<(std::ostream &os, const Time &obj) {
    return os << "'" << std::setw(2) << std::setfill('0') << obj.hour << ":"
              << std::setw(2) << std::setfill('0') << obj.minute << ":"
              << std::setw(2) << std::setfill('0') << obj.second << "'";
  }
};

struct DateTime {
  int year{0};
  int month{0};
  int day{0};
  int hour{0};
  int minute{0};
  int second{0};
  inline constexpr static const char *const fmt = "{}-{}-{} {}:{}:{}";
  DateTime(const std::string &s = "0000-00-00 00:00:00") {
    sscan(fmt, s, year, month, day, hour, minute, second);
  }
  std::string get_db_type() const { return "DATETIME"; }
  std::string get_db_default() const { return "'0000-00-00 00:00:00'"; }
  friend std::ostream &operator<<(std::ostream &os, const DateTime &obj) {
    return os << "'" << std::setw(4) << std::setfill('0') << obj.year << "-"
              << std::setw(2) << std::setfill('0') << obj.month << "-"
              << std::setw(2) << std::setfill('0') << obj.day << " "
              << std::setw(2) << std::setfill('0') << obj.hour << ":"
              << std::setw(2) << std::setfill('0') << obj.minute << ":"
              << std::setw(2) << std::setfill('0') << obj.second << "'";
  }
};

struct Timestamp {
  int year{0};
  int month{0};
  int day{0};
  int hour{0};
  int minute{0};
  int second{0};
  inline constexpr static const char *const fmt = "{}-{}-{} {}:{}:{}";
  Timestamp(const std::string &s = "0000-00-00 00:00:00") {
    sscan(fmt, s, year, month, day, hour, minute, second);
  }
  std::string get_db_type() const { return "TIMESTAMP"; }
  std::string get_db_default() const { return "'0000-00-00 00:00:00'"; }
  friend std::ostream &operator<<(std::ostream &os, const Timestamp &obj) {
    return os << "'" << std::setw(4) << std::setfill('0') << obj.year << "-"
              << std::setw(2) << std::setfill('0') << obj.month << "-"
              << std::setw(2) << std::setfill('0') << obj.day << " "
              << std::setw(2) << std::setfill('0') << obj.hour << ":"
              << std::setw(2) << std::setfill('0') << obj.minute << ":"
              << std::setw(2) << std::setfill('0') << obj.second << "'";
  }
};

struct Year {
  int year{0};
  Year(const std::string &s = "0000") { sscan("{}", s, year); }
  std::string get_db_type() const { return "YEAR"; }
  std::string get_db_default() const { return "'0000'"; }
  friend std::ostream &operator<<(std::ostream &os, const Year &obj) {
    return os << "'" << std::setw(4) << std::setfill('0') << obj.year << "'";
  }
};

struct Char {
  int capacity{0};
  std::string s;
  Char(int vcapacity = 64, const char *const def_s = "") {
    capacity = vcapacity;
    s = def_s;
  }
  std::string get_db_type() const {
    return "CHAR(" + std::to_string(capacity) + ")";
  }
  std::string get_db_default() const { return "''"; }
  friend std::ostream &operator<<(std::ostream &os, const Char &obj) {
    return os << "'" << obj.s << "'";
  }
};

struct Varchar {
  int capacity{0};
  std::string s;
  Varchar(int vcapacity = 64, const char *const def_s = "") {
    capacity = vcapacity;
    s = def_s;
  }
  std::string get_db_type() const {
    return "VARCHAR(" + std::to_string(capacity) + ")";
  }
  std::string get_db_default() const { return "''"; }
  friend std::ostream &operator<<(std::ostream &os, const Varchar &obj) {
    return os << "'" << obj.s << "'";
  }
};

struct Text {
  int capacity{0};
  std::string s;
  Text(int vcapacity = 64, const char *const def_s = "") {
    capacity = vcapacity;
    s = def_s;
  }
  std::string get_db_type() const {
    return "TEXT(" + std::to_string(capacity) + ")";
  }
  std::string get_db_default() const { return "''"; }
  friend std::ostream &operator<<(std::ostream &os, const Text &obj) {
    return os << "'" << obj.s << "'";
  }
};

struct TinyText {
  int capacity{0};
  std::string s;
  TinyText(int vcapacity = 64, const char *const def_s = "") {
    capacity = vcapacity;
    s = def_s;
  }
  std::string get_db_type() const {
    return "TINYTEXT(" + std::to_string(capacity) + ")";
  }
  std::string get_db_default() const { return "''"; }
  friend std::ostream &operator<<(std::ostream &os, const TinyText &obj) {
    return os << "'" << obj.s << "'";
  }
};

struct MediumText {
  int capacity{0};
  std::string s;
  MediumText(int vcapacity = 64, const char *const def_s = "") {
    capacity = vcapacity;
    s = def_s;
  }
  std::string get_db_type() const {
    return "MEDIUMTEXT(" + std::to_string(capacity) + ")";
  }
  std::string get_db_default() const { return "''"; }
  friend std::ostream &operator<<(std::ostream &os, const MediumText &obj) {
    return os << "'" << obj.s << "'";
  }
};

struct LongText {
  int capacity{0};
  std::string s;
  LongText(int vcapacity = 64, const char *const def_s = "") {
    capacity = vcapacity;
    s = def_s;
  }
  std::string get_db_type() const {
    return "LONGTEXT(" + std::to_string(capacity) + ")";
  }
  std::string get_db_default() const { return "''"; }
  friend std::ostream &operator<<(std::ostream &os, const LongText &obj) {
    return os << "'" << obj.s << "'";
  }
};

struct Binary {
  int capacity{0};
  std::string s;
  Binary(int vcapacity = 64, const char *const def_s = "") {
    capacity = vcapacity;
    s = def_s;
  }
  std::string get_db_type() const {
    return "BINARY(" + std::to_string(capacity) + ")";
  }
  std::string get_db_default() const { return "''"; }
  friend std::ostream &operator<<(std::ostream &os, const Binary &obj) {
    return os << "'" << obj.s << "'";
  }
};

struct VarBinary {
  int capacity{0};
  std::string s;
  VarBinary(int vcapacity = 64, const char *const def_s = "") {
    capacity = vcapacity;
    s = def_s;
  }
  std::string get_db_type() const {
    return "VARBINARY(" + std::to_string(capacity) + ")";
  }
  std::string get_db_default() const { return "''"; }
  friend std::ostream &operator<<(std::ostream &os, const VarBinary &obj) {
    return os << "'" << obj.s << "'";
  }
};

struct TinyBlob {
  int capacity{0};
  std::string s;
  TinyBlob(int vcapacity = 64, const char *const def_s = "") {
    capacity = vcapacity;
    s = def_s;
  }
  std::string get_db_type() const {
    return "TINYBLOB(" + std::to_string(capacity) + ")";
  }
  std::string get_db_default() const { return "''"; }
  friend std::ostream &operator<<(std::ostream &os, const TinyBlob &obj) {
    return os << "'" << obj.s << "'";
  }
};

struct Blob {
  int capacity{0};
  std::string s;
  Blob(int vcapacity = 64, const char *const def_s = "") {
    capacity = vcapacity;
    s = def_s;
  }
  std::string get_db_type() const {
    return "BLOB(" + std::to_string(capacity) + ")";
  }
  std::string get_db_default() const { return "''"; }
  friend std::ostream &operator<<(std::ostream &os, const Blob &obj) {
    return os << "'" << obj.s << "'";
  }
};

struct MediumBlob {
  int capacity{0};
  std::string s;
  MediumBlob(int vcapacity = 64, const char *const def_s = "") {
    capacity = vcapacity;
    s = def_s;
  }
  std::string get_db_type() const {
    return "MEDIUMBLOB(" + std::to_string(capacity) + ")";
  }
  std::string get_db_default() const { return "''"; }
  friend std::ostream &operator<<(std::ostream &os, const MediumBlob &obj) {
    return os << "'" << obj.s << "'";
  }
};

struct LongBlob {
  int capacity{0};
  std::string s;
  LongBlob(int vcapacity = 64, const char *const def_s = "") {
    capacity = vcapacity;
    s = def_s;
  }
  std::string get_db_type() const {
    return "LONGBLOB(" + std::to_string(capacity) + ")";
  }
  std::string get_db_default() const { return "''"; }
  friend std::ostream &operator<<(std::ostream &os, const LongBlob &obj) {
    return os << "'" << obj.s << "'";
  }
};

struct Enum {
  std::vector<std::string> candidate;
  std::string value;
  template <typename... ArgsT> Enum(ArgsT &&...args) {
    (candidate.push_back(args), ...);
  }
  std::string get_db_type() const {
    std::string type("ENUM(");
    std::size_t i = 0;
    for (const auto &e : candidate) {
      if (i + 1 < candidate.size()) {
        type.append("'").append(e).append("',");
      } else {
        type.append("'").append(e).append("'");
      }
      i += 1;
    }
    type.append(")");
    return type;
  }
  std::string get_db_default() const { return "null"; }
  friend std::ostream &operator<<(std::ostream &os, const Enum &obj) {
    return os << "'" << obj.value << "'";
  }
};

struct Json {
  nlohmann::json value;
  Json(const std::string &v = "") {
    if (!v.empty()) {
      value = nlohmann::json::parse(v);
    }
  }
  std::string get_db_type() const { return "JSON"; }
  std::string get_db_default() const { return "null"; }
  friend std::ostream &operator<<(std::ostream &os, const Json &obj) {
    return os << "'" << obj.value.dump() << "'";
  }
};

/*
 * Database engine
 */
enum class _DbEngine {
  InnoDB = 1, // InnoDB
  MyISAM,     // MyISAM
  Memory,     // Memory
};

struct _DbEngineConst {
  _DbEngine Innodb{_DbEngine::InnoDB};
  _DbEngine MyISAM{_DbEngine::MyISAM};
  _DbEngine Memory{_DbEngine::Memory};
};

template <typename T> struct _Dml; // Data Manipulation Language
template <typename T> struct _Ddl; // Data Definition Language

/**
 * Conditional expression
 */
struct _CondExpr {
  std::string stmt;
};

/**
 * expression 'and'
 */
inline static _CondExpr operator&&(const _CondExpr &a, const _CondExpr &b) {
  return _CondExpr{a.stmt + " and " + b.stmt};
}
/**
 * expression 'or'
 */
inline static _CondExpr operator||(const _CondExpr &a, const _CondExpr &b) {
  return _CondExpr{a.stmt + " or " + b.stmt};
}

inline static std::string _quote(const std::string &s) { return "`" + s + "`"; }

inline static void _alias(std::string &s) { s = _quote(s).append("."); }

struct _db_field_is_null {};

// null
inline constexpr _db_field_is_null db_null;

// order by
enum class OrderBySort {
  sort_none,
  sort_asc,  // ASC
  sort_desc, // DESC
};

/**
 * conditional statement
 */
struct _SQLCondition {
  inline constexpr static OrderBySort asc =
      OrderBySort::sort_asc; ///< expose to user
  inline constexpr static OrderBySort desc =
      OrderBySort::sort_desc; ///< expose to user
  std::string alias_;         ///< table alias name
  /**
   * Get field getter with alias table name like `a`.`b`
   */
  std::string get_alias() {
    std::string s(alias_);
    alias_.clear();
    return s;
  }

  inline static _DbEngineConst engine;
};
/**
 * base class of update SQL generator
 */
struct _SQLUpdate {
  std::array<bool, 64>
      dirty_array; ///< Dirty flag array for all field, support 64 fields
  /**
   * dtor
   */
  virtual ~_SQLUpdate() {}
  /**
   * set struct pointer
   *
   * \param ptr struct pointer
   */
  virtual void set_ptr(void *ptr) = 0;
  /**
   * get name vetor need to update
   *
   * \param vec name vetor
   */
  virtual void get_update_fields(std::vector<std::string> &vec) = 0;
  /**
   * get value string vector need to insert
   *
   * \param vec name vetor
   */
  virtual void get_insert_fields(std::vector<std::string> &vec) = 0;
  /**
   * get field type name vector of all table field
   *
   * \param vec name vetor
   */
  virtual void get_field_types(std::vector<std::string> &vec) = 0;
  /**
   * get field value string vector of all table field
   *
   * \param vec value string vector
   */
  virtual void get_field_default_values(std::vector<std::string> &vec) = 0;
  /**
   * get field name vector of all table field
   *
   * \param vec name vector
   */
  virtual const std::vector<std::string> &get_field_names() = 0;
  /**
   * set dirty boolean flag of table field
   *
   * \param index field index of mysql_db_table or mysql_db_table_no_init macro
   */
  void set_dirty(std::size_t index) { dirty_array[index] = true; }
  /**
   * check dirty boolean flag of table field
   *
   * \param index field index of mysql_db_table or mysql_db_table_no_init macro
   */
  bool is_dirty(std::size_t index) { return dirty_array[index]; }
  /**
   * clear all dirty boolean flag
   */
  void clear() { dirty_array.fill(false); }
  /**
   * push key=value to update vector
   *
   * \param vec vector
   * \param name field name
   * \param v field value
   */
  template <typename T>
  void push_update(std::vector<std::string> &vec, const char *name,
                   const T &v) {
    vec.push_back(sformat("`{}`={}", name, v));
  }
  /**
   * push key=value to update vector - std::string
   *
   * \param vec vector
   * \param name field name
   * \param v field value
   */
  template <>
  void push_update(std::vector<std::string> &vec, const char *name,
                   const std::string &v) {
    vec.push_back(sformat("`{}`='{}'", name, v));
  }
  /**
   * push field value string to insersion vector
   *
   * \param vec vector
   * \param v field value
   */
  template <typename T>
  void push_insert(std::vector<std::string> &vec, const T &v) {
    vec.push_back(sformat("{}", v));
  }
  /**
   * push field value string to insersion vector - std::string
   *
   * \param vec vector
   * \param v field value
   */
  template <>
  void push_insert(std::vector<std::string> &vec, const std::string &v) {
    vec.push_back(sformat("'{}'", v));
  }
};

/**
 * The condition placeholder of struct field
 */
struct _FieldCondition {
  std::string field_name;            ///< field name
  _SQLCondition *cond_ptr_{nullptr}; ///< _SQLCondition instance

  /**
   * Set field name
   */
  void set_name(const std::string &name) { field_name = "`" + name + "`"; }
  /**
   * get field name
   */
  const std::string &get_name() const { return field_name; }
  /**
   * Set _SQLCondition pointer
   */
  void set_cond_ptr(_SQLCondition *cond) { cond_ptr_ = cond; }
  /**
   * get alias.name if has
   */
  std::string get_alias_name() const {
    return cond_ptr_->get_alias() + get_name();
  }
  /**
   * operator '<'
   */
  template <typename ArgT> inline _CondExpr operator<(const ArgT &v) {
    return _CondExpr{get_alias_name() + " < " + sformat("{}", v)};
  }
  /**
   * operator '<='
   */
  template <typename ArgT> inline _CondExpr operator<=(const ArgT &v) {
    return kratos::util::db::_mysql::_CondExpr{get_alias_name() +
                                               " <= " + sformat("{}", v)};
  }
  /**
   * operator '>'
   */
  template <typename ArgT> inline _CondExpr operator>(const ArgT &v) {
    return _CondExpr{get_alias_name() + " > " + sformat("{}", v)};
  }
  /**
   * operator '>='
   */
  template <typename ArgT> inline _CondExpr operator>=(const ArgT &v) {
    return _CondExpr{get_alias_name() + " >= " + sformat("{}", v)};
  }
  /**
   * operator '!='
   */
  template <typename ArgT> inline _CondExpr operator!=(const ArgT &v) {
    return _CondExpr{get_alias_name() + " <> " + sformat("{}", v)};
  }
  /**
   * operator 'like'
   */
  inline _CondExpr like(const std::string &s) {
    return _CondExpr{get_alias_name() + " like '" + s + "'"};
  }
  /**
   * is not null
   */
  inline _CondExpr operator!=(const _db_field_is_null &v) {
    return _CondExpr{get_alias_name() + " is not null"};
  }
  /**
   * operator 'is null'
   */
  inline _CondExpr operator==(const _db_field_is_null &v) {
    return _CondExpr{get_alias_name() + " is null"};
  }
  /**
   * operator '='
   */
  template <typename ArgT> inline _CondExpr operator==(const ArgT &v) {
    return _CondExpr{get_alias_name() + " = " + sformat("{}", v)};
  }
};

/*
 * DML
 */
template <typename T> struct _Dml {
  /*
   * Select clause
   */
  struct _DmlSelect {
  private:
    class Pusher {
      std::vector<std::string> *vec_name_{nullptr}; ///< name vector

    public:
      Pusher(std::vector<std::string> *vec_name) { vec_name_ = vec_name; }
      template <typename ItemType>
      inline void operator()(const ItemType &item) {
        vec_name_->emplace_back(item.get_name());
      }
    };
    // TODO variable stack to support recursive select clause
    std::string cond_str_;
    std::string from_str_;
    std::string from_as_;
    std::string limit_str_;
    std::string group_or_order_by_;
    std::vector<std::string> col_names_;

    std::string build_stmt(bool term = false) {
      std::string cols;
      std::size_t i = 0;
      for (const auto &c : col_names_) {
        if (i + 1 < col_names_.size()) {
          cols.append(c).append(",");
        } else {
          cols.append(c);
        }
        i += 1;
      }
      std::string stmt;
      if (col_names_.empty()) {
        if (from_str_.empty()) {
          stmt.append("select * from ")
              .append(T::get_name())
              .append(cond_str_)
              .append(group_or_order_by_)
              .append(term ? ";" : "");
        } else {
          stmt.append("select * from (")
              .append(from_str_)
              .append(")")
              .append(from_as_)
              .append(cond_str_)
              .append(group_or_order_by_)
              .append(term ? ";" : "");
        }
      } else {
        if (from_str_.empty()) {
          stmt.append("select ")
              .append(cols)
              .append(" from ")
              .append(T::get_name())
              .append(cond_str_)
              .append(group_or_order_by_)
              .append(term ? ";" : "");
        } else {
          stmt.append("select ")
              .append(cols)
              .append(" from (")
              .append(from_str_)
              .append(")")
              .append(from_as_)
              .append(cond_str_)
              .append(group_or_order_by_)
              .append(term ? ";" : "");
        }
      }
      cond_str_.clear();
      from_str_.clear();
      from_as_.clear();
      group_or_order_by_.clear();
      col_names_.clear();
      return stmt;
    }

  public:
    template <typename... ArgsT> _DmlSelect &Column(ArgsT &...args) {
      auto tuple = std::make_tuple(args...);
      kratos::util::tuple_foreach(tuple, Pusher(&col_names_));
      return *this;
    }
    template <typename NameT> _DmlSelect &GroupBy(const NameT &cond_stmt) {
      group_or_order_by_.append(" group by ")
          .append(cond_stmt.get_alias_name());
      return *this;
    }
    template <typename NameT>
    _DmlSelect &OrderBy(const NameT &cond_stmt,
                        OrderBySort sort = OrderBySort::sort_none) {
      if (sort == OrderBySort::sort_asc) {
        group_or_order_by_.append(" order by ")
            .append(cond_stmt.get_alias_name())
            .append(" asc");
      } else if (sort == OrderBySort::sort_desc) {
        group_or_order_by_.append(" order by ")
            .append(cond_stmt.get_alias_name())
            .append(" desc");
      }
      return *this;
    }
    _DmlSelect &From(_DmlSelect &stmt) {
      from_str_.append(stmt.build_stmt());
      return *this;
    }
    _DmlSelect &from_as(_DmlSelect &stmt, const std::string &alias) {
      from_as_.append(" as ").append(alias);
      from_str_.append(stmt.build_stmt());
      return *this;
    }
    _DmlSelect &Limit(std::size_t n) {
      limit_str_.append(" limit ").append(std::to_string(n));
      return *this;
    }
    _DmlSelect &Limit(std::size_t From, std::size_t to) {
      limit_str_.append(" limit ")
          .append(std::to_string(From))
          .append(",")
          .append(std::to_string(to));
      return *this;
    }
    _DmlSelect &limit_offset(std::size_t n, std::size_t offset) {
      limit_str_.append(" limit ")
          .append(std::to_string(n))
          .append(" offset ")
          .append(std::to_string(offset));
      return *this;
    }

  public:
    std::string Build() { return build_stmt(true); }

    // TODO Where x in sub-query
    _DmlSelect &Where(const _CondExpr &cond_stmt) {
      cond_str_.append(" where ").append(cond_stmt.stmt);
      return *this;
    }
  };
  /*
   * Update clause
   */
  struct _DmlUpdate {
  private:
    std::string cond_str_;
    std::string order_by_;
    std::string limit_str_;
    T *ptr_{nullptr};

  public:
    void set_ptr(T *ptr) {
      ptr_ = ptr;
      _Dml::get_updater().set_ptr(ptr_);
    }
    _DmlUpdate &Where(const _CondExpr &cond_stmt) {
      cond_str_.append(" where ").append(cond_stmt.stmt);
      return *this;
    }
    template <typename NameT>
    _DmlUpdate &OrderBy(const NameT &cond_stmt,
                        OrderBySort sort = OrderBySort::sort_none) {
      if (sort == OrderBySort::sort_asc) {
        order_by_.append(" order by ")
            .append(cond_stmt.get_alias_name())
            .append(" asc");
      } else if (sort == OrderBySort::sort_desc) {
        order_by_.append(" order by ")
            .append(cond_stmt.get_alias_name())
            .append(" desc");
      }
      return *this;
    }
    _DmlUpdate &Limit(std::size_t n) {
      limit_str_.append(" limit ").append(std::to_string(n));
      return *this;
    }
    std::string Build() {
      std::string result("update ");
      result.append(T::get_name()).append(" set ");
      std::vector<std::string> field_list_vec;
      _Dml::get_updater().get_update_fields(field_list_vec);
      if (field_list_vec.empty()) {
        return "";
      }
      std::size_t i = 0;
      for (const auto &c : field_list_vec) {
        if (i + 1 < field_list_vec.size()) {
          result += c + ",";
        } else {
          result += c;
        }
        i += 1;
      }
      result += cond_str_ + order_by_ + limit_str_;
      result.append(";");
      cond_str_.clear();
      order_by_.clear();
      _Dml::get_updater().clear();
      return result;
    }
  };
  /*
   * Insert clause
   */
  struct _DmlInsert {
  private:
    T *ptr_{nullptr};

  public:
    std::string Build() {
      const std::vector<std::string> &name_array =
          _Dml::get_updater().get_field_names();
      std::vector<std::string> vec;
      _Dml::get_updater().get_insert_fields(vec);
      if (vec.empty()) {
        return "";
      }
      std::size_t i = 0;
      std::string result("insert into ");
      result.append(ptr_->get_name()).append(" (");
      for (const auto &name : name_array) {
        if (i + 1 < name_array.size()) {
          result.append(name).append(",");
        } else {
          result.append(name);
        }
        i += 1;
      }
      result.append(") values (");
      i = 0;
      for (const auto &field_name : vec) {
        if (i + 1 < vec.size()) {
          result.append(field_name).append(",");
        } else {
          result.append(field_name);
        }
        i += 1;
      }
      result.append(");");
      return result;
    }
    void set_ptr(T *ptr) {
      ptr_ = ptr;
      _Dml::get_updater().set_ptr(ptr_);
    }
  };
  /*
   * Delete clause
   */
  struct _DmlDelete {
  private:
    std::string cond_str_;
    std::string from_str_;
    std::string from_as_;
    std::string limit_str_;
    std::string order_by_;

  public:
    template <typename NameT>
    _DmlDelete &OrderBy(const NameT &cond_stmt,
                        OrderBySort sort = OrderBySort::sort_none) {
      if (sort == OrderBySort::sort_asc) {
        order_by_.append(" order by ")
            .append(cond_stmt.get_alias_name())
            .append(" asc");
      } else if (sort == OrderBySort::sort_desc) {
        order_by_.append(" order by ")
            .append(cond_stmt.get_alias_name())
            .append(" desc");
      }
      return *this;
    }
    _DmlDelete &Limit(std::size_t n) {
      limit_str_.append(" limit ").append(std::to_string(n));
      return *this;
    }
    _DmlDelete &Where(const _CondExpr &cond_stmt) {
      cond_str_.append(" where ").append(cond_stmt.stmt);
      return *this;
    }
    std::string Build() {
      std::string result("delete from ");
      result.append(T::get_name())
          .append(cond_str_)
          .append(order_by_)
          .append(limit_str_)
          .append(";");
      return result;
    }
  };

  inline T &operator()(T &o) {
    update(o);
    return o;
  }

  template <typename... ArgsT> static _DmlSelect &Select(ArgsT &...args) {
    static _DmlSelect s;
    return s.Column(args...);
  }

  static _DmlUpdate &Update(T &obj) {
    static _DmlUpdate u;
    u.set_ptr(&obj);
    return u;
  }

  static _DmlInsert &Insert(T &obj) {
    static _DmlInsert i;
    i.set_ptr(&obj);
    return i;
  }

  static _DmlDelete &Delete() {
    static _DmlDelete d;
    return d;
  }

  typedef typename T::SQLCondition SQLCondition;
  typedef typename T::SQLUpdate SQLUpdate;

  SQLCondition &Condition() {
    static SQLCondition c;
    return c;
  }

  static SQLUpdate &get_updater() {
    static SQLUpdate s;
    return s;
  }

private:
  friend struct _DmlSelect;
  friend struct _DmlInsert;
  friend struct _DmlUpdate;
  friend struct _DmlDelete;

  template <typename StructT> friend struct _Ddl;
};

template <typename T> struct _Ddl {
  typedef typename T::SQLUpdate SQLUpdate;
  typedef typename T::SQLCondition SQLCondition;
  friend struct _DdlCreate;

  struct _DdlCreate {
  private:
    std::string engine_{"InnoDB"};
    std::string charset_{"utf8"};
    std::string primary_key_;
    T o_;

  public:
    _DdlCreate &Engine(_DbEngine engine) {
      if (engine == _DbEngine::InnoDB) {
        engine_ = "InnoDB";
      } else if (engine == _DbEngine::MyISAM) {
        engine_ = "MyISAM";
      } else if (engine == _DbEngine::Memory) {
        engine_ = "Memory";
      }
      return *this;
    }
    _DdlCreate &PrimaryKey(const _FieldCondition &cond_stmt) {
      primary_key_.append("primary key (")
          .append(cond_stmt.field_name)
          .append(")");
      return *this;
    }
    std::string Build() {
      _Dml<T>::get_updater().set_ptr(&o_);
      const auto &vec = _Dml<T>::get_updater().get_field_names();
      if (vec.empty()) {
        return "";
      }
      std::size_t i = 0;
      std::string result;
      result.append("create table if not exists ")
          .append(T::get_name())
          .append("(");
      std::vector<std::string> type_vec;
      _Dml<T>::get_updater().get_field_types(type_vec);
      std::vector<std::string> default_vec;
      _Dml<T>::get_updater().get_field_default_values(default_vec);
      for (const auto &field_name : vec) {
        result.append(field_name)
            .append(" ")
            .append(type_vec[i])
            .append(default_vec[i]);
        if (i + 1 < vec.size()) {
          result.append(",");
        } else {
          if (!primary_key_.empty()) {
            result.append(",");
          }
        }
        i += 1;
      }
      result.append(primary_key_)
          .append(")")
          .append(" engine=")
          .append(engine_)
          .append(" default CHARSET=")
          .append(charset_);
      result.append(";");
      return result;
    }
  };

  struct _DdlAlter {};

  _DdlCreate &Create(_DbEngine e = _DbEngine::InnoDB) {
    static _DdlCreate c;
    c.Engine(e);
    return c;
  }

  SQLCondition &Condition() {
    static SQLCondition c;
    return c;
  }

  std::string Use(const std::string &db_name) { return "use " + db_name + ";"; }
};

template <typename T> inline static std::string get_field_db_type(const T &v) {
  return v.get_db_type();
}

#define std_cont_get_field_db_type_key(type)                                   \
  template <typename KeyT>                                                     \
  inline static std::string get_field_db_type(const type<KeyT> &v) {           \
    return "JSON";                                                             \
  }

std_cont_get_field_db_type_key(std::vector);
std_cont_get_field_db_type_key(std::list);
std_cont_get_field_db_type_key(std::queue);
std_cont_get_field_db_type_key(std::deque);
std_cont_get_field_db_type_key(std::forward_list);
std_cont_get_field_db_type_key(std::set);
std_cont_get_field_db_type_key(std::multiset);
std_cont_get_field_db_type_key(std::unordered_multiset);
std_cont_get_field_db_type_key(std::unordered_set);
std_cont_get_field_db_type_key(std::initializer_list);

#define std_cont_get_field_db_type_key_value(type)                             \
  template <typename KeyT, typename ValueT>                                    \
  inline static std::string get_field_db_type(const type<KeyT, ValueT> &v) {   \
    return "JSON";                                                             \
  }

std_cont_get_field_db_type_key_value(std::map);
std_cont_get_field_db_type_key_value(std::unordered_map);
std_cont_get_field_db_type_key_value(std::multimap);
std_cont_get_field_db_type_key_value(std::unordered_multimap);

template <> inline std::string get_field_db_type(const std::int8_t &) {
  return "TINYINT";
}

inline static std::string get_field_db_type(const std::int16_t &) {
  return "SMALLINT";
}

inline static std::string get_field_db_type(const std::int32_t &) {
  return "INT";
}

inline static std::string get_field_db_type(const std::int64_t &) {
  return "BIGINT";
}

inline static std::string get_field_db_type(const std::uint8_t &) {
  return "TINYINT UNSIGNED";
}

inline static std::string get_field_db_type(const std::uint16_t &) {
  return "SMALLINT UNSIGNED";
}

inline static std::string get_field_db_type(const std::uint32_t &) {
  return "INT UNSIGNED";
}

inline static std::string get_field_db_type(const std::uint64_t &) {
  return "BIGINT UNSIGNED";
}

inline static std::string get_field_db_type(const bool &) { return "BOOLEAN"; }

inline static std::string get_field_db_type(const float &) { return "FLOAT"; }

inline static std::string get_field_db_type(const double &) { return "DOUBLE"; }

inline static std::string get_field_db_type(const std::string &) {
  return "VARCHAR(64)";
}

// TODO default type
// TODO MYSQL SPECIAL TYPE
template <typename T> inline static std::string get_field_default(const T &v) {
  return " DEFAULT " + v.get_db_default();
}

#define std_cont_get_field_default_key(type)                                   \
  template <typename KeyT>                                                     \
  inline static std::string get_field_default(const type<KeyT> v) {            \
    return " DEFAULT null";                                                    \
  }

std_cont_get_field_default_key(std::vector);
std_cont_get_field_default_key(std::list);
std_cont_get_field_default_key(std::queue);
std_cont_get_field_default_key(std::deque);
std_cont_get_field_default_key(std::forward_list);
std_cont_get_field_default_key(std::set);
std_cont_get_field_default_key(std::unordered_set);
std_cont_get_field_default_key(std::multiset);
std_cont_get_field_default_key(std::unordered_multiset);
std_cont_get_field_default_key(std::initializer_list);

#define std_cont_get_field_default_key_value(type)                             \
  template <typename KeyT, typename ValueT>                                    \
  inline static std::string get_field_default(const type<KeyT, ValueT> v) {    \
    return " DEFAULT null";                                                    \
  }

std_cont_get_field_default_key_value(std::map);
std_cont_get_field_default_key_value(std::unordered_map);
std_cont_get_field_default_key_value(std::multimap);
std_cont_get_field_default_key_value(std::unordered_multimap);

template <> inline std::string get_field_default(const std::int8_t &v) {
  auto s = sformat("'{}'", v);
  if (!s.empty()) {
    return " DEFAULT " + s;
  }
  return "";
}

template <> inline std::string get_field_default(const std::int16_t &v) {
  auto s = sformat("'{}'", v);
  if (!s.empty()) {
    return " DEFAULT " + s;
  }
  return "";
}

template <> inline std::string get_field_default(const std::int32_t &v) {
  auto s = sformat("'{}'", v);
  if (!s.empty()) {
    return " DEFAULT " + s;
  }
  return "";
}

template <> inline std::string get_field_default(const std::int64_t &v) {
  auto s = sformat("'{}'", v);
  if (!s.empty()) {
    return " DEFAULT " + s;
  }
  return "";
}

template <> inline std::string get_field_default(const std::uint8_t &v) {
  auto s = sformat("'{}'", v);
  if (!s.empty()) {
    return " DEFAULT " + s;
  }
  return "";
}

template <> inline std::string get_field_default(const std::uint16_t &v) {
  auto s = sformat("'{}'", v);
  if (!s.empty()) {
    return " DEFAULT " + s;
  }
  return "";
}

template <> inline std::string get_field_default(const std::uint32_t &v) {
  auto s = sformat("'{}'", v);
  if (!s.empty()) {
    return " DEFAULT " + s;
  }
  return "";
}

template <> inline std::string get_field_default(const std::uint64_t &v) {
  auto s = sformat("'{}'", v);
  if (!s.empty()) {
    return " DEFAULT " + s;
  }
  return "";
}

template <> inline std::string get_field_default(const bool &v) {
  auto s = sformat("'{}'", v);
  if (!s.empty()) {
    return " DEFAULT " + s;
  }
  return "";
}

template <> inline std::string get_field_default(const float &v) {
  auto s = sformat("'{}'", v);
  if (!s.empty()) {
    return " DEFAULT " + s;
  }
  return "";
}

template <> inline std::string get_field_default(const double &v) {
  auto s = sformat("'{}'", v);
  if (!s.empty()) {
    return " DEFAULT " + s;
  }
  return "";
}

template <> inline std::string get_field_default(const std::string &v) {
  auto s = sformat("'{}'", v);
  if (!s.empty()) {
    return " DEFAULT " + s;
  }
  return "";
}

} // namespace _mysql

// Expose MySQL utility
template <typename T> struct _MySQL {
  template <typename... ArgsT>
  inline static decltype(auto) Select(ArgsT &...args) {
    return _mysql::_Dml<T>().Select(args...);
  }
  template <typename... ArgsT>
  inline static decltype(auto) Insert(ArgsT &...args) {
    return _mysql::_Dml<T>().Insert(args...);
  }
  inline static decltype(auto) Delete() { return _mysql::_Dml<T>().Delete(); }
  template <typename... ArgsT>
  inline static decltype(auto) Update(ArgsT &...args) {
    return _mysql::_Dml<T>().Update(args...);
  }
  inline static decltype(auto) Conditon() {
    return _mysql::_Dml<T>().Condition();
  }
  inline static decltype(Conditon()) c = Conditon();
  inline static decltype(auto) Create() { return _mysql::_Ddl<T>().Create(); }
  template <typename... ArgsT> inline static decltype(auto) Use(ArgsT... args) {
    return _mysql::_Ddl<T>().Use(args...);
  }
};

using kratos::util::db::_mysql::_alias;
using kratos::util::db::_mysql::_quote;

using kratos::util::db::_mysql::_CondExpr;
using kratos::util::db::_mysql::_Ddl;
using kratos::util::db::_mysql::_Dml;
using kratos::util::db::_mysql::_SQLCondition;
using kratos::util::db::_mysql::_SQLUpdate;

using kratos::util::db::_mysql::Date;
using kratos::util::db::_mysql::DateTime;
using kratos::util::db::_mysql::Time;
using kratos::util::db::_mysql::Timestamp;
using kratos::util::db::_mysql::Year;

using kratos::util::db::_mysql::Binary;
using kratos::util::db::_mysql::Blob;
using kratos::util::db::_mysql::Char;
using kratos::util::db::_mysql::LongText;
using kratos::util::db::_mysql::MediumBlob;
using kratos::util::db::_mysql::MediumText;
using kratos::util::db::_mysql::Text;
using kratos::util::db::_mysql::TinyBlob;
using kratos::util::db::_mysql::TinyText;
using kratos::util::db::_mysql::VarBinary;
using kratos::util::db::_mysql::Varchar;

using kratos::util::db::_mysql::Enum;

using kratos::util::db::_mysql::Json;

} // namespace db
} // namespace util
} // namespace kratos

template <typename T> using MySQL = kratos::util::db::_MySQL<T>;
namespace mysql = kratos::util::db::_mysql;

#define mysql_condition_field(n) kratos::util::db::_mysql::_FieldCondition n;

#define mysql_condition_field_list(...)                                        \
  DB_EXPAND(DB_PASTE(mysql_condition_field, __VA_ARGS__))

#define mysql_field_name_init(n)                                               \
  n.set_name(#n);                                                              \
  n.set_cond_ptr(this);

#define mysql_field_name_init_list(...)                                        \
  DB_EXPAND(DB_PASTE(mysql_field_name_init, __VA_ARGS__))

#define mysql_db_field(n) __add_field(#n);

#define mysql_db_set_member(n)                                                 \
  void set_##n(decltype(n) &&v) {                                              \
    n = v;                                                                     \
    DML::get_updater().set_dirty(__##n##_index__);                             \
  }

#define mysql_db_all_set_member(...)                                           \
  DB_EXPAND(DB_PASTE(mysql_db_set_member, __VA_ARGS__));

#define mysql_db_update_set(n)                                                 \
  if (is_dirty(i++)) {                                                         \
    push_update(vec, #n, obj_ptr_->n);                                         \
  }

#define mysql_db_update_check(...)                                             \
  std::size_t i = 0;                                                           \
  DB_EXPAND(DB_PASTE(mysql_db_update_set, __VA_ARGS__));

#define mysql_db_field_type(n)                                                 \
  vec.push_back(kratos::util::db::_mysql::get_field_db_type(obj_ptr_->n));

#define mysql_db_all_field_type(...)                                           \
  DB_EXPAND(DB_PASTE(mysql_db_field_type, __VA_ARGS__));

#define mysql_db_insert_field(n) push_insert(vec, obj_ptr_->n);

#define mysql_db_insert_all_field(...)                                         \
  DB_EXPAND(DB_PASTE(mysql_db_insert_field, __VA_ARGS__))

#define mysql_db_fields(...) DB_EXPAND(DB_PASTE(mysql_db_field, __VA_ARGS__))

#define mysql_db_init(...)                                                     \
  if (!__db_init_flag) {                                                       \
    __db_init_flag = __db_initialize();                                        \
    mysql_db_init_all_member_index(__VA_ARGS__);                               \
  }

#define mysql_db_member_index(n) inline static std::uint8_t __##n##_index__{0};

#define mysql_db_member_all_index(...)                                         \
  DB_EXPAND(DB_PASTE(mysql_db_member_index, __VA_ARGS__));

#define mysql_db_init_member_index(n) __##n##_index__ = i++;

#define mysql_db_init_all_member_index(...)                                    \
  std::uint8_t i = 0;                                                          \
  DB_EXPAND(DB_PASTE(mysql_db_init_member_index, __VA_ARGS__))                 \
  __db_init_flag = true;

#define mysql_db_field_default_value(n)                                        \
  vec.push_back(kratos::util::db::_mysql::get_field_default(obj_ptr_->n));

#define mysql_db_all_field_default_value(...)                                  \
  DB_EXPAND(DB_PASTE(mysql_db_field_default_value, __VA_ARGS__));

#define mysql_db_table(tbl_name, ...)                                          \
public:                                                                        \
  mysql_db_all_set_member(__VA_ARGS__);                                        \
                                                                               \
private:                                                                       \
  template <typename T> friend struct kratos::util::db::_Dml;                  \
  template <typename T> friend struct kratos::util::db::_Ddl;                  \
  using DML = kratos::util::db::_Dml<tbl_name>;                                \
  using DDL = kratos::util::db::_Ddl<tbl_name>;                                \
                                                                               \
  mysql_db_member_all_index(__VA_ARGS__);                                      \
  inline static bool __db_init_flag = false;                                   \
  inline static std::shared_ptr<std::vector<std::string>> __name_vec;          \
  inline static const std::string &get_name() {                                \
    static std::string name(kratos::util::db::_quote(#tbl_name));              \
    return name;                                                               \
  }                                                                            \
  inline static const std::vector<std ::string> &get_field_names() {           \
    return *__name_vec;                                                        \
  }                                                                            \
  inline static void __add_field(const char *name) {                           \
    if (!__name_vec) {                                                         \
      __name_vec = std::make_shared<std::vector<std::string>>();               \
    }                                                                          \
    __name_vec->push_back(kratos::util::db::_quote(std::string(name)));        \
  }                                                                            \
  inline bool static __db_initialize() {                                       \
    mysql_db_fields(__VA_ARGS__);                                              \
    return true;                                                               \
  }                                                                            \
  struct SQLUpdate : public kratos::util::db::_SQLUpdate {                     \
    tbl_name *obj_ptr_{nullptr};                                               \
    virtual ~SQLUpdate() {}                                                    \
    SQLUpdate() { dirty_array.fill(false); }                                   \
    virtual void set_ptr(void *ptr) override { obj_ptr_ = (tbl_name *)ptr; }   \
    virtual void get_update_fields(std::vector<std::string> &vec) override {   \
      mysql_db_update_check(__VA_ARGS__);                                      \
    }                                                                          \
    virtual void get_insert_fields(std::vector<std::string> &vec) override {   \
      mysql_db_insert_all_field(__VA_ARGS__);                                  \
    }                                                                          \
    virtual const std::vector<std::string> &get_field_names() override {       \
      return *tbl_name::__name_vec;                                            \
    }                                                                          \
    virtual void get_field_types(std::vector<std::string> &vec) override {     \
      mysql_db_all_field_type(__VA_ARGS__);                                    \
    }                                                                          \
    virtual void                                                               \
    get_field_default_values(std::vector<std::string> &vec) override {         \
      mysql_db_all_field_default_value(__VA_ARGS__);                           \
    }                                                                          \
    static kratos::util::db::_SQLUpdate *get_updater() {                       \
      static SQLUpdate u;                                                      \
      return &u;                                                               \
    }                                                                          \
    static const std::string &get_name() { return tbl_name::get_name(); }      \
  };                                                                           \
  struct SQLCondition : public kratos::util::db::_SQLCondition {               \
    mysql_condition_field_list(__VA_ARGS__);                                   \
    SQLCondition() { mysql_field_name_init_list(__VA_ARGS__); }                \
    SQLCondition &operator()(const std::string &alias) {                       \
      kratos::util::db::_alias(alias_);                                        \
      return *this;                                                            \
    }                                                                          \
    kratos::util::db::_CondExpr                                                \
    operator()(const kratos::util::db::_CondExpr &cs) {                        \
      return kratos::util::db::_CondExpr{"(" + cs.stmt + ")"};                 \
    }                                                                          \
  };                                                                           \
  friend struct SQLUpdate;                                                     \
  friend struct SQLCondition;                                                  \
                                                                               \
public:                                                                        \
  tbl_name() { mysql_db_init(__VA_ARGS__); }

#define mysql_db_table_no_init(tbl_name, ...)                                  \
public:                                                                        \
  mysql_db_all_set_member(__VA_ARGS__);                                        \
                                                                               \
private:                                                                       \
  template <typename T> friend struct kratos::util::db::_Dml;                  \
  template <typename T> friend struct kratos::util::db::_Ddl;                  \
  using DML = kratos::util::db::_Dml<tbl_name>;                                \
  using DDL = kratos::util::db::_Ddl<tbl_name>;                                \
                                                                               \
  mysql_db_member_all_index(__VA_ARGS__);                                      \
  inline static bool __db_init_flag = false;                                   \
  inline static std::shared_ptr<std::vector<std::string>> __name_vec;          \
  inline static const std::string &get_name() {                                \
    static std::string name(kratos::util::db::_quote(#tbl_name));              \
    return name;                                                               \
  }                                                                            \
  inline static const std::vector<std ::string> &get_field_names() {           \
    return *__name_vec;                                                        \
  }                                                                            \
  inline static void __add_field(const char *name) {                           \
    if (!__name_vec) {                                                         \
      __name_vec = std::make_shared<std::vector<std::string>>();               \
    }                                                                          \
    __name_vec->push_back(kratos::util::db::_quote(std::string(name)));        \
  }                                                                            \
  inline bool static __db_initialize() {                                       \
    mysql_db_fields(__VA_ARGS__);                                              \
    return true;                                                               \
  }                                                                            \
  struct SQLUpdate : public kratos::util::db::_SQLUpdate {                     \
    tbl_name *obj_ptr_{nullptr};                                               \
    virtual ~SQLUpdate() {}                                                    \
    SQLUpdate() { dirty_array.fill(false); }                                   \
    virtual void set_ptr(void *ptr) override { obj_ptr_ = (tbl_name *)ptr; }   \
    virtual void get_update_fields(std::vector<std::string> &vec) override {   \
      mysql_db_update_check(__VA_ARGS__);                                      \
    }                                                                          \
    virtual void get_insert_fields(std::vector<std::string> &vec) override {   \
      mysql_db_insert_all_field(__VA_ARGS__);                                  \
    }                                                                          \
    virtual const std::vector<std::string> &get_field_names() override {       \
      return *tbl_name::__name_vec;                                            \
    }                                                                          \
    virtual void get_field_types(std::vector<std::string> &vec) override {     \
      mysql_db_all_field_type(__VA_ARGS__);                                    \
    }                                                                          \
    virtual void                                                               \
    get_field_default_values(std::vector<std::string> &vec) override {         \
      mysql_db_all_field_default_value(__VA_ARGS__);                           \
    }                                                                          \
    static kratos::util::db::_SQLUpdate *get_updater() {                       \
      static SQLUpdate u;                                                      \
      return &u;                                                               \
    }                                                                          \
    static const std::string &get_name() { return tbl_name::get_name(); }      \
  };                                                                           \
  struct SQLCondition : public kratos::util::db::_SQLCondition {               \
    mysql_condition_field_list(__VA_ARGS__);                                   \
    SQLCondition() { mysql_field_name_init_list(__VA_ARGS__); }                \
    SQLCondition &operator()(const std::string &alias) {                       \
      kratos::util::db::_alias(alias_);                                        \
      return *this;                                                            \
    }                                                                          \
    kratos::util::db::_CondExpr                                                \
    operator()(const kratos::util::db::_CondExpr &cs) {                        \
      return kratos::util::db::_CondExpr{"(" + cs.stmt + ")"};                 \
    }                                                                          \
  };                                                                           \
  friend struct SQLUpdate;                                                     \
  friend struct SQLCondition;                                                  \
                                                                               \
public:
